<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

require_once('modules/Opportunities/Opportunity.php');
require_once('include/SugarObjects/templates/person/Person.php');

class logic_hooks_class extends Opportunity {
	/*
		Get lead is by opp id
	*/
	function get_lead_id($Opps_Id)
	{
	   	// module specific
		$sql = "Select id from leads where opportunity_id = '$Opps_Id'";
		$result = $this->db->query($sql,true);
		//echo $sql;
		while($row = $this->db->fetchByAssoc($result)){
			return $row["id"];
		}
	}
			/*
		Get num of item by Sales_Stage and Lead_Id
	*/
	function get_no_of_items_updated_lead($Sales_Stage, $Lead_Id)
	{
	   	// module specific
		$sql = "Select count(id) as count
		from Leads_Checklist where Checklist_Response <>'' and Sales_Stage= '$Sales_Stage' and Lead_Id = '$Lead_Id'";
		$result = $this->db->query($sql,true);
		//echo $sql;
		while($row = $this->db->fetchByAssoc($result)){
			return $row["count"];
		}
	}
	/*
		Get num of item by Sales_Stage and Lead_Id
	*/
	function get_num_of_item_lead($Sales_Stage, $Lead_Id)
	{
	   	// module specific
	   	$sql = "Select count(id) as count
			from Leads_Checklist where Sales_Stage= '$Sales_Stage' and Lead_Id = '$Lead_Id'";
		$result = $this->db->query($sql,true);
		
		while($row = $this->db->fetchByAssoc($result)){
			return $row["count"];
		}
	}
   
   
	/*
		Get num of item by Sales_Stage and Opps_Id
	*/
	function get_no_of_items_updated($Sales_Stage, $Opps_Id)
	{
	   	// module specific
		$sql = "Select count(id) as count
		from Opps_Checklist where Checklist_Response <>'' and Sales_Stage= '$Sales_Stage' and Opps_Id = '$Opps_Id'";
		$result = $this->db->query($sql,true);
		//echo $sql;
		while($row = $this->db->fetchByAssoc($result)){
			return $row["count"];
		}
	}
	/*
		Get num of item by Sales_Stage and Opps_Id
	*/
	function get_num_of_item($Sales_Stage, $Opps_Id)
	{
	   	// module specific
	   	$sql = "Select count(id) as count
			from Opps_Checklist where Sales_Stage= '$Sales_Stage' and Opps_Id = '$Opps_Id'";
		$result = $this->db->query($sql,true);
		
		while($row = $this->db->fetchByAssoc($result)){
			return $row["count"];
		}
	}
   
	/*
		Get sumary all Sales_Stage
	*/
	function get_sumary($Opps_Id, $lead_id)
	{
	   	// module specific
		$sql = "Select distinct '' as id, Sales_Stage,0 as No_of_items, 0 as No_of_items_updated,Module_Name
		from Lead_Opp_Checklist_Master where Module_Name = 'Opps' order by Sales_Stage";
		$result = $this->db->query($sql,true);
		
		while($row = $this->db->fetchByAssoc($result)){
		//$value = $_REQUEST['Checklist_Response_'.$item['id']];
			$row["id"] = $Opps_Id;
			$row["No_of_items"] = $this->get_num_of_item($row["Sales_Stage"], $Opps_Id);
			$row["No_of_items_updated"] = $this->get_no_of_items_updated($row["Sales_Stage"], $Opps_Id);
			$sumary[] = $row;
		}
		
		if($lead_id != "")
		{
			// module specific
			$sql = "Select distinct '' as id, Sales_Stage,0 as No_of_items, 0 as No_of_items_updated, Module_Name
			from Lead_Opp_Checklist_Master where Module_Name = 'Leads' order by Sales_Stage";
			$result = $this->db->query($sql,true);
			
			while($row = $this->db->fetchByAssoc($result)){
			//$value = $_REQUEST['Checklist_Response_'.$item['id']];
				$row["id"] = $lead_id;
				$row["No_of_items"] = $this->get_num_of_item_lead($row["Sales_Stage"], $lead_id);
				$row["No_of_items_updated"] = $this->get_no_of_items_updated_lead($row["Sales_Stage"], $lead_id);
				$sumary[] = $row;
			}
		}
		
		return $sumary;
	}
	
  /*
  	Download excel file
  */
  function download_file($focus, $lead_id)
  {
	   	ob_clean();
		ob_start();
		$header = "Opps_Name,Sales_Stage, Checklist Description,Response";
		
	 	// module specific
		$sql = "Select '".$focus->name."' as Name, m.Sales_Stage, m.Checklist_Item_Desc,l.Checklist_Response
		from Opps_Checklist l inner join Lead_Opp_Checklist_Master m on m.id = l.Lead_Opp_Checklist_Master_id
		where m.Module_Name = 'Opps' and l.Opps_Id ='".$_REQUEST['record']."'";
		$result = $this->db->query($sql,true);
		$data = '';
		while($row = $this->db->fetchByAssoc($result)){
		
		    $line = '';
			foreach($row as $value){
			  if(!isset($value) || $value == ""){
			         $value = ",";
			  }else{
			         $value = str_replace('"', '""', $value);
			         $value = '"' . $value . '"' . ",";
			         }
			  $line .= $value;
			}
			$data .= trim($line)."\n";
			$data = str_replace("\r", "", $data);

			if ($data == "" && $lead_id == "") {
				$data = "\nno matching records found\n";
			}
		}
		if($lead_id != "")
		{
			// module specific
			$sql = "Select '".$focus->first_name." ".$focus->last_name."' as Name, m.Sales_Stage, m.Checklist_Item_Desc,l.Checklist_Response
			from Leads_Checklist l inner join Lead_Opp_Checklist_Master m on m.id = l.Lead_Opp_Checklist_Master_id
			where m.Module_Name = 'Leads' and l.Lead_Id ='".$_REQUEST['record']."'";
			$result = $this->db->query($sql,true);
			while($row = $this->db->fetchByAssoc($result)){
			
				//$value = $_REQUEST['Checklist_Response_'.$item['id']];
			    $line = '';
				foreach($row as $value){
				  if(!isset($value) || $value == ""){
				         $value = ",";
				  }else{
				         $value = str_replace('"', '""', $value);
				         $value = '"' . $value . '"' . ",";
				         }
				  $line .= $value;
				}
				$data .= trim($line)."\n";
				$data = str_replace("\r", "", $data);

				if ($data == "") {
					$data = "\nno matching records found\n";
				}
			}
		
		}
		
		header("Pragma: no-cache");
		header("Expires: 0");
		header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
		header("Content-type: application/vnd.ms-excel; name='excel'");
		header('Content-Disposition: attachment; filename="Opps_Checklist.xls"');
		header('Content-Length: '.strlen($header."\n".$data));
		
		// output data
		echo $header."\n".$data;
		exit(); //done outputting csv
  }
     
 	 function after_save_method($bean, $event, $arguments)
    {
    	global $current_user;
        //logic
    	if(isset($_REQUEST['record'])) {
			//display logic
    		$sql = "select * from Opps_Checklist where Opps_Id ='{$bean->id}'";
			$result = $this->db->limitQuery($sql,0,1,true, "Want only a single row");
			$row= $this->db->fetchByAssoc($result);
			
			if(empty($row)){
			 	//create insert query for new campaign log
                $insert_query="insert into Opps_Checklist(Opps_Id,Sales_Stage,Lead_Opp_Checklist_Master_id,Checklist_Response)
				select '{$bean->id}', Sales_Stage, id,'' 
				 from Lead_Opp_Checklist_Master where Module_Name = 'Opps'";
                $this->db->query($insert_query);
			}
		}
    }
    function after_ui_frame_method($event, $arguments)
        {
        	//echo "fadsfds".$_REQUEST['action'];
        	global $current_user;
        
			if(isset($_REQUEST['record'])) {
				//display logic
	          	if($_REQUEST['action']=='DetailView') {
	          		$focus=new Opportunity();
					$focus->retrieve($_REQUEST['record']);
					
	          		if($_REQUEST['use_adv'] == 'Checklist')
	          		{
	          				// module specific
							$sql = "Select id from Opps_Checklist where Opps_Id ='".$_REQUEST['record']."'";
							$result = $this->db->query($sql,true);
							$value = '';
							$update_query = '';
							while($item   = $this->db->fetchByAssoc($result)){
								$value = $_REQUEST['Checklist_Response_'.$item['id']];
							    //create insert query for new campaign log
				                $update_query ="update Opps_Checklist set Checklist_Response = '{$value}'
				                	where id = '{$item['id']}';";
				        	 	$this->db->query($update_query);        
							}
							
							if($_REQUEST['lead_id'] != "")
							{
								// module specific
								$sql = "Select id	from Leads_Checklist where Lead_Id ='".$_REQUEST['lead_id']."'";
								$result = $this->db->query($sql,true);
								$value = '';
								$update_query = '';
								while($item   = $this->db->fetchByAssoc($result)){
									$value = $_REQUEST['Checklist_Response_'.$item['id']];
								    //create insert query for new campaign log
					                $update_query ="update Leads_Checklist set Checklist_Response = '{$value}'
					                	where id = '{$item['id']}';";
					        	 	$this->db->query($update_query);        
								}
							}
	          		}
	          		else if($_REQUEST['use_adv'] == 'excel')
	          		{
	          			$this->download_file($focus, $_REQUEST['lead_id']);
	          		}
	          		
	          		
					
					// module specific
					$sql = "Select l.id, m.Module_Name, Opps_Id, m.Sales_Stage,m.Allowed_Responses,m.Checklist_Item_Type,m.Checklist_Item_Desc,m.Checklist_Item_Name,
						m.no_of_rows, l.Checklist_Response
					from Opps_Checklist l inner join Lead_Opp_Checklist_Master m on m.id = l.Lead_Opp_Checklist_Master_id
					where m.Module_Name = 'Opps' and l.Opps_Id ='".$_REQUEST['record']."'";
					$result = $this->db->query($sql,true);
					while($item   = $this->db->fetchByAssoc($result)){
					    $checklists[] = $item;
					}
					// get check list from leads module
					$lead_id = $this->get_lead_id($_REQUEST['record']);
					if($lead_id != "")
					{
						// module specific
						$sql = "Select l.id, m.Module_Name, Lead_Id as Opps_Id, m.Sales_Stage,m.Allowed_Responses,m.Checklist_Item_Type,m.Checklist_Item_Desc,m.Checklist_Item_Name,
								m.no_of_rows, l.Checklist_Response
						from Leads_Checklist l inner join Lead_Opp_Checklist_Master m on m.id = l.Lead_Opp_Checklist_Master_id
						where m.Module_Name = 'Leads' and l.Lead_Id ='$lead_id'";
						$result = $this->db->query($sql,true);
						while($item   = $this->db->fetchByAssoc($result)){
						    $checklists[] = $item;
						}
					}
					$this->createFormHTML($focus,$checklists, $lead_id);
				}
			}
        }
        
        
	function createFormHTML($focus,$checklists, $lead_id) {
		global $mod_strings;
		global $app_list_strings;
		global $app_strings;
		global $current_user;

		// TEMPLATE ASSIGNMENTS
		$xtpl = new XTemplate('custom/modules/Opportunities/CheckListEditView.html');
		// standard assigns
		$xtpl->assign('MOD', $mod_strings);
		$xtpl->assign('APP', $app_strings);
		
		$xtpl->assign('RETURN_MODULE', 'Opportunities');
		$xtpl->assign('ID', $focus->id);
		$xtpl->assign('lead_id', $lead_id);
		$xtpl->assign('RETURN_ID', $focus->id);
		$xtpl->assign('RETURN_ACTION', 'DetailView');
		 // another way to do it would be:
        $rowsize = count($checklists);
        for ($i = 0; $i < $rowsize; $i++) {
        // assign array data
        	$xtpl->assign('data', $checklists[$i]);
        	$val = '';
        	
        	if($checklists[$i]['Checklist_Item_Type'] == 'Dropdown')
        	{
        		$val = "<select name='Checklist_Response_".$checklists[$i]['id']."' style=\"width: 200px\">";
        		$arr = null;
        		if($checklists[$i]['Allowed_Responses'] <> '')
        		{
	        		$list = explode( ',', ','.$checklists[$i]['Allowed_Responses'] );
	        		//create the type dropdown domain and set the selected value if $opp value already exists
					foreach ($list as $key => $value) {
					    // echo "Key: $key; Value: $value<br />\n";
					    $arr[$value] = $value;
					}
        			$val .=get_select_options_with_id($arr , $checklists[$i]['Checklist_Response']);
        		}
        		$val .="</select>";
        		
        	}
        	else
        	{
        		$val = "<textarea name='Checklist_Response_".$checklists[$i]['id']."' rows=\"".$checklists[$i]['no_of_rows']
        			."\" cols=\"100\">".$checklists[$i]['Checklist_Response']."</textarea>";
        	}
        	
        	$val =$val. "<input type='hidden' name='Module_Name_".$checklists[$i]['id']."' value='".$checklists[$i]['Module_Name']."'>";
        	$xtpl->assign('Checklist_Response', $val);
        	
        	 // parse a row
	        $xtpl->parse('main.table.row');
		}
		
		$sumary = $this->get_sumary($focus->id, $lead_id);
		$rowsize = count($sumary);
	 	for ($i = 0; $i < $rowsize; $i++) {
	        // assign array data
	        	$xtpl->assign('sumary', $sumary[$i]);
	        	 // parse a row
		        $xtpl->parse('main.table2.row2');
		}

		$xtpl->assign('STATUS', $focus->sales_stage);
		$sales = null; 
		$lead_status_dom = $app_list_strings['lead_status_dom'];
		$sales_stage_dom = $app_list_strings['sales_stage_dom'];
		
		$list = array_merge($lead_status_dom, $sales_stage_dom);
		asort($list);
		
		foreach ($list as $key => $value) {
		    $sales[$value] = $value;
		}

		$xtpl->assign('STATUS_OPTIONS',get_select_options_with_id($sales , $focus->sales_stage));
		 // parse the table
	    $xtpl->parse('main.table');
	    $xtpl->parse('main.table2');
		$xtpl->parse("main");
		$xtpl->out("main");
	}

	
    }

?>